﻿Imports System.Data
Partial Class Content_Reportes_rep_controls_Reporte_inversión_disponible
    Inherits System.Web.UI.UserControl
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim IControles As New Clases.controles
    Dim IDataBase As New Clases.DataBase

    
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            Dim q1, q2, q3 As String
            Dim dt, dt1, dt2 As New DataTable



            q1 = "Select * from TDespachos where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlDespacho, "TDespachos", "desp_id", "desp_nombre", q1)
            IDataBase.createDataTable(dt, q1)
            If dt.Rows.Count <= 0 Then
                IControles.addNoneAllElement(ddlDespacho)
            End If


            q2 = "Select * from TInstituciones where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlInstitucion, "TInstituciones", "inst_id", "inst_nombre", q2)
            IDataBase.createDataTable(dt, q2)
            If dt.Rows.Count <= 0 Then
                IControles.addNoneAllElement(ddlInstitucion)
            End If


            q3 = "Select * from tEmisoras where stat_id=1"
            IControles.fill_dropDownListBaseQuery(ddlEmisora, "tEmisoras", "emi_id", "emi_nombre", q3)
            IDataBase.createDataTable(dt, q3)
            If dt.Rows.Count <= 0 Then
                IControles.addNoneAllElement(ddlEmisora)
            End If
            

        End If

    End Sub


    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
        Dim q1, q2 As String
        If ddlTipoO.SelectedValue = "Compra" Then

            gvReporteInvDisponibleCompra.Columns(12).Visible = True
            q1 = "select Despacho, Institución, Cliente, Contrato, Perfil, Emisora, Tipo, Titulos, Costo, [Valor Mercado], [Porcentaje de Inversión], [Valor Cartera], [Porcentaje Faltante], Faltante, Permitido, [Porcentaje Total Para Comprar] from vwReporteInvDisp where 1=1"
            q2 = "select sum(Faltante)as suma from vwReporteInvDisp where 1=1"
            If ddlDespacho.SelectedValue <> "None" Then
                q1 = q1 & " and desp_id=" & ddlDespacho.SelectedValue & ""
                q2 = q2 & " and desp_id=" & ddlDespacho.SelectedValue & ""
            End If

            If ddlInstitucion.SelectedValue <> "None" Then
                q1 = q1 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
                q2 = q2 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
            End If

            If ddlEmisora.SelectedValue <> "None" Then
                q1 = q1 & " and emi_id=" & ddlEmisora.SelectedValue & ""
                q2 = q2 & " and emi_id=" & ddlEmisora.SelectedValue & ""
            End If

            ltotal.Visible = "true"
            total.Visible = "true"
            total.Text = Replace(IDataBase.getSimpleData(q2, "suma"), ",", ".")

        Else
            gvReporteInvDisponibleCompra.Columns(12).Visible = False
            q1 = "select Despacho, Institución, Cliente, Contrato, Perfil, Emisora, Tipo, Titulos, Costo, [Valor Mercado], [Porcentaje de Inversión], [Valor Cartera], FaltanteVenta as Faltante,Permitido, [Porcentaje Total Para Venta] as [Porcentaje Total Para Comprar] from vwReporteInvDisp where 1=1"
            q2 = "select sum(FaltanteVenta) as suma from vwReporteInvDisp where 1=1"
            If ddlDespacho.SelectedValue <> "None" Then
                q1 = q1 & " and desp_id=" & ddlDespacho.SelectedValue & ""
                q2 = q2 & " and desp_id=" & ddlDespacho.SelectedValue & ""
            End If

            If ddlInstitucion.SelectedValue <> "None" Then
                q1 = q1 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
                q2 = q2 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
            End If

            If ddlEmisora.SelectedValue <> "None" Then
                q1 = q1 & " and emi_id=" & ddlEmisora.SelectedValue & ""
                q2 = q2 & " and emi_id=" & ddlEmisora.SelectedValue & ""
            End If

            If ddlDespacho.SelectedValue <> "None" Then
                q1 = q1 & " and desp_id=" & ddlDespacho.SelectedValue & ""
                q2 = q2 & " and desp_id=" & ddlDespacho.SelectedValue & ""
            End If

            If ddlInstitucion.SelectedValue <> "None" Then
                q1 = q1 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
                q2 = q2 & " and inst_id=" & ddlInstitucion.SelectedValue & ""
            End If

            If ddlEmisora.SelectedValue <> "None" Then
                q1 = q1 & " and emi_id=" & ddlEmisora.SelectedValue & ""
                q2 = q2 & " and emi_id=" & ddlEmisora.SelectedValue & ""
            End If

            ltotal.Visible = "true"
            total.Visible = "true"
            total.Text = Replace(IDataBase.getSimpleData(q2, "suma"), ",", ".")

        End If

        Session("RepMovi") = q1
        href_disponible.Value = q1
        IControles.fillGridViewPaging(gvReporteInvDisponibleCompra, q1)
        btn_excel.Visible = True
    End Sub

    Protected Sub gvReporteInvDisponibleCompra_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvReporteInvDisponibleCompra.PageIndexChanging
        IControles.fillGridViewPaging(gvReporteInvDisponibleCompra, href_disponible.Value, Convert.ToInt32(e.NewPageIndex))
    End Sub

    Protected Sub exportToExcel(ByVal query As String)
        Dim gridView_toExport As New GridView
        IControles.fillGridViewPaging(gridView_toExport, Session("RepMovi"))

        For i As Integer = 0 To gridView_toExport.Rows.Count - 1
            Dim row As GridViewRow = gridView_toExport.Rows(i)
        Next

        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As IO.StringWriter = New IO.StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page
        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)
        gridView_toExport.AllowPaging = False
        form.Controls.Add(gridView_toExport)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"

        Response.AddHeader("Content-Disposition", "attachment;filename=InvDisp" & Now.ToString("_dd_MMMM_yy_HH_mm_ss") & ".xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btn_excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_excel.Click
        exportToExcel(Session("RepMovi"))
    End Sub
End Class
